Certain relational database systems allow reading and writing the same data set at the same time. This is accomplished by temporarily saving the old data value when the data set is changed and there is at least one other transaction currently active. Saving the old data value allows the relational database to provide the old value to other transactions attempting to read the just changed data set. This concept (also referred to as “multi version read consistency”) enables the principle that “readers never block writers and writers never block readers”.
A conventional database operates with plain lock-based read consistency; i.e., when a transaction changes a data set, the data set is locked with an X lock (exclusive). Other transactions are now blocked when attempting to access this data set. The X lock remains on the changed data set until the transaction that performed the change finishes. This leads to the principle behavior that “readers block writers and writers block readers”.
This approach introduces problems with concurrent access. Transactions that just want to read data sets potentially have to wait until other transactions that changed these data sets finish. This can lead to an overall degradation of performance even though the system resources (CPU, disk etc.) are only partially occupied. This general problem is called “lock contention”.
There can also be transactions that perform read and write access in a mixed manner. These transactions can lead to deadlocks such as in the following simple scenario: transaction 1 changes data set A, transaction 2 changes data set B, transaction 2 reads data set A, transaction 1 reads data set B, transaction 2 reads data set A. Both transactions are now waiting for an X lock of the other transaction. Such situations may be resolved by explicitly rolling back one of the involved transactions. “Rollback” means that all the changes of a transaction are undone.
The application has to react on deadlock error messages of the database system by, for example, starting a new transaction and reissuing all the statements.
Deadlocks worsen the overall performance significantly because it generally takes some time (seconds) for the database system to recognize a deadlock situation and resolve the deadlock situation with a rollback operation. The rolled back transaction is typically started from the beginning, further degrading database performance.
What is therefore needed is a system, a computer program product, and an associated method for controlling access to a database. The need for such a solution has heretofore remained unsatisfied.